/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.icee.myth.server.dbHandler;

import com.google.protobuf.CodedInputStream;
import com.icee.myth.common.charInfo.CharOccupyInfo;
import com.icee.myth.common.charInfo.CharDetailInfo;
import com.icee.myth.common.messageQueue.ServerMessageQueue;
import com.icee.myth.config.MapConfig;
import com.icee.myth.server.GameServer;
import com.icee.myth.server.social.BriefPlayerInfo;
import com.icee.myth.log.GameLogger;
import com.icee.myth.log.message.FileDebugGameLogMessage;
import com.icee.myth.log.message.builder.GameLogMessageBuilder;
import com.icee.myth.protobuf.ExternalCommonProtocol.CreateCharProto;
import com.icee.myth.protobuf.ExternalCommonProtocol.RewardProto;
import com.icee.myth.protobuf.InternalCommonProtocol.DBMailsProto;
import com.icee.myth.protobuf.InternalCommonProtocol.DBOccupyInfoProto;
import com.icee.myth.protobuf.InternalCommonProtocol.DBRelationProto;
import com.icee.myth.protobuf.InternalCommonProtocol.PlayerDetailProto;
import com.icee.myth.server.activity.normalActivity.NormalActivityTemplate;
import com.icee.myth.server.mail.Mail;
import com.icee.myth.server.message.dbMessage.AddNormalActivityDBMessage;
import com.icee.myth.server.message.dbMessage.DeleteNormalActivityDBMessage;
import com.icee.myth.server.message.serverMessage.builder.MapMessageBuilder;
import com.icee.myth.server.reward.CertainRewardInfo;
import com.icee.myth.utils.Consts;
import com.icee.myth.utils.JSONHelper;
import com.icee.myth.utils.StackTraceUtil;
import com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException;
import java.io.IOException;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.LinkedList;

/**
 *
 * @author liuxianke
 */
public class MapDBHandler {

    /**
     * 获取帐号角色数
     * @param connection
     * @param playerId
     * @return false 表示数据库已断连
     */
    public static boolean handleGetCharNum(Connection connection, int playerId) {
        CallableStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = connection.prepareCall("{call get_char_num(?)}");

            stmt.setInt(1, playerId);
            rs = stmt.executeQuery();

            int charNum = -1;
            if (rs.next()) {
                charNum = rs.getInt(1);
            }

            ServerMessageQueue.queue().offer(MapMessageBuilder.buildCharNumMessage(playerId, charNum));
        } catch (MySQLNonTransientConnectionException ex) {
            // 数据库断线
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
            return false;
        } catch (SQLException ex) {
            // 其他异常
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
                }
            }
        }

        return true;
    }

    /**
     * 创建角色
     * @param connection
     * @param playerId
     * @param createCharInfo
     * @return false表示数据库断连
     */
    public static boolean handleCreateChar(Connection connection, int playerId, CreateCharProto createCharInfo) {
        CallableStatement stmt = null;
        try {
            stmt = connection.prepareCall("{call create_char(?,?,?)}");
            stmt.setInt(1, playerId);                   //mid
            stmt.setString(2, createCharInfo.getName());    //name
            int leaderCardId = MapConfig.INSTANCE.initCardIds[createCharInfo.getJob()];
            stmt.setInt(3, leaderCardId);     //leader card
            
            stmt.executeUpdate();

            // 创建角色成功
            ServerMessageQueue.queue().offer(MapMessageBuilder.buildCreateCharRetMessage(playerId, 1));

            // 写创建角色日志
            GameLogger.getlogger().log(GameLogMessageBuilder.buildDBCreateCharGameLogMessage(playerId, leaderCardId));
        } catch (MySQLNonTransientConnectionException ex) {
            // 数据库断线
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
            return false;
        } catch (SQLException ex) {
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));

            // 创建角色失败
            ServerMessageQueue.queue().offer(MapMessageBuilder.buildCreateCharRetMessage(playerId, -1));
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
                }
            }
        }

        return true;
    }

    /**
     * 获取角色详细信息
     * @param connection
     * @param playerId
     * @return false表示数据库断连
     */
    public static boolean handleGetCharDetail(Connection connection, int playerId) {
        CallableStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = connection.prepareCall("{call get_char_detail(?)}");
            stmt.setInt(1, playerId);
            rs = stmt.executeQuery();

            CharDetailInfo charInfo = new CharDetailInfo();
            if (rs.next()) {
                charInfo.mid = rs.getInt(1);
                charInfo.name = rs.getString(2);
                charInfo.gold1 = rs.getInt(3);
                charInfo.gold2 = rs.getInt(4);
                charInfo.silver = rs.getLong(5);
                charInfo.energy = rs.getInt(6);
                charInfo.token = rs.getInt(7);
                charInfo.level = rs.getInt(8);
                charInfo.experience = rs.getInt(9);
                charInfo.rankLevel = rs.getInt(10);
                charInfo.rankExperience = rs.getInt(11);
                charInfo.leaderCardId = rs.getInt(12);
                charInfo.leaderCardLevel = rs.getInt(13);
                charInfo.vipLevel = rs.getInt(14);
                charInfo.vipExperience = rs.getInt(15);
                charInfo.maxPower = rs.getInt(16);
                //处理Blob数据
                Blob charDetailBlob = rs.getBlob(17);
                if (charDetailBlob != null) {
                    try {
                        charInfo.detail = PlayerDetailProto.getDefaultInstance().newBuilderForType().mergeFrom(CodedInputStream.newInstance(charDetailBlob.getBinaryStream())).build();
                    } catch (IOException ex) {
                        GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));

                        // TODO: 获取信息失败
                    }
                }

                Blob mailBlob = rs.getBlob(18);
                if (mailBlob != null) {
                    try {
                        charInfo.mailInfo = DBMailsProto.getDefaultInstance().newBuilderForType().mergeFrom(CodedInputStream.newInstance(mailBlob.getBinaryStream())).build();
                    } catch (IOException ex) {
                        GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));

                        // TODO: 获取信息失败
                    }
                }

                charInfo.totalOnlineTime = rs.getLong(19);

                Timestamp timestamp = rs.getTimestamp(20);
                charInfo.leaveTime = (timestamp != null) ? timestamp.getTime() : GameServer.INSTANCE.getCurrentTime();

                ServerMessageQueue.queue().offer(MapMessageBuilder.buildGetCharDetailRetMessage(charInfo.mid, charInfo));
            } else {
                GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDebugGameLogMessage(
                        FileDebugGameLogMessage.DebugLogType.DEBUGLOGTYPE_ERROR,
                        "Player[" + playerId + "] not found in database."));
            }
        } catch (MySQLNonTransientConnectionException ex) {
            // 数据库断线
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
            return false;
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));

        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
                }
            }
        }

        return true;
    }

    /**
     * 更新玩家角色详细数据
     * @param connection
     * @param playerId
     * @param info
     * @return false表示数据库连接
     */
    public static boolean handleUpdateCharDetailInfo(Connection connection, int playerId, CharDetailInfo info) {
        CallableStatement stmt = null;
        try {
            stmt = connection.prepareCall("{call update_char_detail_info(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
            stmt.setInt(1, playerId);
            stmt.setInt(2, info.gold1);
            stmt.setInt(3, info.gold2);
            stmt.setLong(4, info.silver);
            stmt.setInt(5, info.energy);
            stmt.setInt(6, info.token);
            stmt.setInt(7, info.level);
            stmt.setInt(8, info.experience);
            stmt.setInt(9, info.rankLevel);
            stmt.setInt(10, info.rankExperience);
            stmt.setInt(11, info.leaderCardId);
            stmt.setInt(12, info.leaderCardLevel);
            stmt.setInt(13, info.vipLevel);
            stmt.setInt(14, info.vipExperience);
            stmt.setInt(15, info.maxPower);

            assert (info.detail != null);
            Blob detailBlob = connection.createBlob();
            byte[] bytes = info.detail.toByteArray();
            detailBlob.setBytes(1, bytes);
            stmt.setBlob(16, detailBlob);

            if (info.mailInfo != null) {
                Blob mailBlob = connection.createBlob();
                bytes = info.mailInfo.toByteArray();
                mailBlob.setBytes(1, bytes);
                stmt.setBlob(17, mailBlob);
            } else {
                stmt.setNull(17, java.sql.Types.BLOB);
            }

            stmt.setLong(18, info.totalOnlineTime);
            stmt.setTimestamp(19, new Timestamp(info.leaveTime));

            stmt.executeUpdate();

            // 记录Debug日志
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDebugGameLogMessage(
                    FileDebugGameLogMessage.DebugLogType.DEBUGLOGTYPE_INFO,
                    "Update char detail info : id[" + playerId + "]."));
        } catch (MySQLNonTransientConnectionException ex) {
            // 数据库断线
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
            return false;
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障

            // TODO: 记录日志
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDebugGameLogMessage(
                    FileDebugGameLogMessage.DebugLogType.DEBUGLOGTYPE_ERROR,
                    "Update char detail info : id[" + playerId + "]."));

            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
                }
            }
        }

        return true;
    }

    /**
     * 获取角色臣属信息
     * @param connection
     * @param playerId
     * @return false表示数据库断连
     */
    public static boolean handleGetCharOccupyInfo(Connection connection, int playerId) {
        CallableStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = connection.prepareCall("{call get_occupy_info(?)}");
            stmt.setInt(1, playerId);
            rs = stmt.executeQuery();

            CharOccupyInfo charOccupyInfo = new CharOccupyInfo();
            if (rs.next()) {
                charOccupyInfo.mid = rs.getInt(1);
                charOccupyInfo.name = rs.getString(2);
                charOccupyInfo.level = rs.getInt(3);
                charOccupyInfo.rank = rs.getInt(4);
                charOccupyInfo.leaderCardId = rs.getInt(5);
                charOccupyInfo.leaderCardLevel = rs.getInt(6);

                Blob charOccupyBlob = rs.getBlob(7);
                if (charOccupyBlob != null) {
                    try {
                        charOccupyInfo.occupyInfo = DBOccupyInfoProto.getDefaultInstance().newBuilderForType().mergeFrom(CodedInputStream.newInstance(charOccupyBlob.getBinaryStream())).build();
                    } catch (IOException ex) {
                        GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));

                        // TODO: 获取信息失败
                    }
                }

                ServerMessageQueue.queue().offer(MapMessageBuilder.buildGetCharOccupyInfoRetMessage(charOccupyInfo.mid, charOccupyInfo));
            } else {
                GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDebugGameLogMessage(
                        FileDebugGameLogMessage.DebugLogType.DEBUGLOGTYPE_ERROR,
                        "Player[" + playerId + "] not found in database."));
            }
        } catch (MySQLNonTransientConnectionException ex) {
            // 数据库断线
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
            return false;
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));

        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
                }
            }
        }

        return true;
    }

    public static boolean handleUpdateCharOccupyInfo(Connection connection, int playerId, DBOccupyInfoProto occupyInfoProto) {
        CallableStatement stmt = null;
        try {
            stmt = connection.prepareCall("{call update_occupy_info(?,?)}");
            stmt.setInt(1, playerId);

            Blob detailBlob = connection.createBlob();
            byte[] bytes = occupyInfoProto.toByteArray();
            detailBlob.setBytes(1, bytes);
            stmt.setBlob(2, detailBlob);

            stmt.executeUpdate();

            // 记录Debug日志
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDebugGameLogMessage(
                    FileDebugGameLogMessage.DebugLogType.DEBUGLOGTYPE_INFO,
                    "Update char occupy info : id[" + playerId + "]."));
        } catch (MySQLNonTransientConnectionException ex) {
            // 数据库断线
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
            return false;
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障

            // TODO: 记录日志
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDebugGameLogMessage(
                    FileDebugGameLogMessage.DebugLogType.DEBUGLOGTYPE_ERROR,
                    "Update char detail info : id[" + playerId + "]."));

            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
                }
            }
        }

        return true;
    }

    public static boolean handleChangePlayerName(Connection connection, int playerId, String name) {
        Statement stmt = null;
        try {
            String sql = "update charactor set name = '" + name + "' where mid = " + playerId;
            stmt = connection.createStatement();
            stmt.execute(sql);

            // 记录Debug日志
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDebugGameLogMessage(
                    FileDebugGameLogMessage.DebugLogType.DEBUGLOGTYPE_INFO,
                    "change char name info : id[" + playerId + "]."));
        } catch (MySQLNonTransientConnectionException ex) {
            // 数据库断线
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
            return false;
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障

            // TODO: 记录日志
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDebugGameLogMessage(
                    FileDebugGameLogMessage.DebugLogType.DEBUGLOGTYPE_ERROR,
                    "change char name info : id[" + playerId + "]."));

            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
                }
            }
        }

        return true;
    }

    public static boolean handleAddNormalActivity(Connection connection, AddNormalActivityDBMessage addNormalActivityDBMessage) {
        Statement stmt = null;
        try {
            NormalActivityTemplate template = JSONHelper.parseString(addNormalActivityDBMessage.addNormalActivityMessage.activityJsonString, NormalActivityTemplate.class);
            String sql = "insert into activity(id,detail) values(" + template.staticInfo.id + ",'" + addNormalActivityDBMessage.addNormalActivityMessage.activityJsonString + "');";
            stmt = connection.createStatement();
            if (stmt.executeUpdate(sql) == 1) {
                addNormalActivityDBMessage.addNormalActivityMessage.response = true;
                // 记录Debug日志
                GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDebugGameLogMessage(
                        FileDebugGameLogMessage.DebugLogType.DEBUGLOGTYPE_INFO,
                        "Add normal activity " + addNormalActivityDBMessage.addNormalActivityMessage.activityJsonString));
            } else {
                GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDebugGameLogMessage(
                        FileDebugGameLogMessage.DebugLogType.DEBUGLOGTYPE_ERROR,
                        "Failed to add normal activity " + addNormalActivityDBMessage.addNormalActivityMessage.activityJsonString));
            }

        } catch (MySQLNonTransientConnectionException ex) {
            // 数据库断线
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
            return false;
        } catch (Exception ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障

            // TODO: 记录日志
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDebugGameLogMessage(
                    FileDebugGameLogMessage.DebugLogType.DEBUGLOGTYPE_ERROR,
                    "Add normal activity " + addNormalActivityDBMessage.addNormalActivityMessage.activityJsonString));

            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
                }
            }

            addNormalActivityDBMessage.addNormalActivityMessage.hasWriteDB = true;
            ServerMessageQueue.queue().add(addNormalActivityDBMessage.addNormalActivityMessage);
        }

        return true;
    }

    public static boolean handleDeleteNormalActivity(Connection connection, DeleteNormalActivityDBMessage deleteNormalActivityDBMessage) {
        Statement stmt = null;
        try {
            String sql = "update activity set enable = 0 where id = " + deleteNormalActivityDBMessage.deleteNormalActivityMessage.activityId;
            stmt = connection.createStatement();
            if (stmt.executeUpdate(sql) == 1) {
                deleteNormalActivityDBMessage.deleteNormalActivityMessage.response = true;
                // 记录Debug日志
                GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDebugGameLogMessage(
                        FileDebugGameLogMessage.DebugLogType.DEBUGLOGTYPE_INFO,
                        "Delete normal activity " + deleteNormalActivityDBMessage.deleteNormalActivityMessage.activityId));
            } else {
                GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDebugGameLogMessage(
                        FileDebugGameLogMessage.DebugLogType.DEBUGLOGTYPE_ERROR,
                        "Failed to delete normal activity " + deleteNormalActivityDBMessage.deleteNormalActivityMessage.activityId));
            }

           
        } catch (MySQLNonTransientConnectionException ex) {
            // 数据库断线
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
            return false;
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
            // TODO: 记录日志
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDebugGameLogMessage(
                    FileDebugGameLogMessage.DebugLogType.DEBUGLOGTYPE_ERROR,
                    "delete normal activity " + deleteNormalActivityDBMessage.deleteNormalActivityMessage.activityId));

            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
                }
            }
            deleteNormalActivityDBMessage.deleteNormalActivityMessage.hasWriteDB = true;
            ServerMessageQueue.queue().add(deleteNormalActivityDBMessage.deleteNormalActivityMessage);
        }

        return true;
    }

    /**
     * 保存好友关系
     * @param connection
     * @param playerId
     * @param relationProto
     * @return false表示数据库连接
     */
    public static boolean handleSaveRelation(Connection connection, int playerId, DBRelationProto relationProto) {
        CallableStatement stmt = null;
        try {
            stmt = connection.prepareCall("{call update_relation(?,?)}");
            stmt.setInt(1, playerId);

            Blob blob = connection.createBlob();
            byte[] bytes = relationProto.toByteArray();
            blob.setBytes(1, bytes);
            stmt.setBlob(2, blob);

            stmt.executeUpdate();

            // 记录Debug日志
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDebugGameLogMessage(
                    FileDebugGameLogMessage.DebugLogType.DEBUGLOGTYPE_INFO,
                    "Update char relation info : id[" + playerId + "]."));
        } catch (MySQLNonTransientConnectionException ex) {
            // 数据库断线
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
            return false;
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障

            // TODO: 记录日志
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDebugGameLogMessage(
                    FileDebugGameLogMessage.DebugLogType.DEBUGLOGTYPE_ERROR,
                    "Update char relation info : id[" + playerId + "] error."));

            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
                }
            }

        }

        return true;
    }

    /**
     * 获取好友关系
     * @param connection
     * @param playerId
     * @return false表示数据库断连
     */
    public static boolean handleGetRelation(Connection connection, int playerId) {
        CallableStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = connection.prepareCall("{call get_relation(?)}");

            stmt.setInt(1, playerId);

            rs = stmt.executeQuery();

            DBRelationProto relationProto = null;
            if (rs.next()) {
                Blob blob = rs.getBlob(1);

                if (blob != null) {
                    try {
                        relationProto = DBRelationProto.getDefaultInstance().newBuilderForType().mergeFrom(CodedInputStream.newInstance(blob.getBinaryStream())).build();
                    } catch (IOException ex) {
                        GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDebugGameLogMessage(
                                FileDebugGameLogMessage.DebugLogType.DEBUGLOGTYPE_ERROR,
                                "Get player[" + playerId + "] relation pause error."));
                        GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDebugGameLogMessage(
                                FileDebugGameLogMessage.DebugLogType.DEBUGLOGTYPE_ERROR,
                                StackTraceUtil.getStackTrace(ex)));
                    }
                }
                ServerMessageQueue.queue().offer(MapMessageBuilder.buildGetRelationRetMessage(playerId, relationProto, true));

                GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDebugGameLogMessage(
                        FileDebugGameLogMessage.DebugLogType.DEBUGLOGTYPE_INFO,
                        "Get player[" + playerId + "] relation."));
            } else {
                ServerMessageQueue.queue().offer(MapMessageBuilder.buildGetRelationRetMessage(playerId, relationProto, false));

                GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDebugGameLogMessage(
                        FileDebugGameLogMessage.DebugLogType.DEBUGLOGTYPE_ERROR,
                        "Get player[" + playerId + "] relation error because no such player."));
            }
        } catch (MySQLNonTransientConnectionException ex) {
            // 数据库断线
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
            return false;
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障

            // TODO: 记录日志
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDebugGameLogMessage(
                    FileDebugGameLogMessage.DebugLogType.DEBUGLOGTYPE_ERROR,
                    "Get player[" + playerId + "] relation error."));

            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
                }
            }
        }

        return true;
    }

    /**
     * 获取简略玩家信息列表
     * @param connection
     * @param playerId
     * @param ids
     * @return false表示数据库断连
     */
    public static boolean handleGetBriefPlayerInfos(Connection connection, int playerId, ArrayList<Integer> ids) {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            StringBuilder stringBuilder = new StringBuilder("SELECT mid,name,level,leaderCardId,leaderCardLevel FROM charactor WHERE mid IN (");
            int idNum = ids.size();
            for (int i = 0; i < idNum - 1; i++) {
                stringBuilder.append(ids.get(i)).append(',');
            }
            stringBuilder.append(ids.get(idNum - 1)).append(')');

            stmt = connection.prepareStatement(stringBuilder.toString());

            rs = stmt.executeQuery();

            LinkedList<BriefPlayerInfo> briefPlayerInfos = new LinkedList<BriefPlayerInfo>();
            while (rs.next()) {
                briefPlayerInfos.add(new BriefPlayerInfo(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getInt(5)));
            }

            ServerMessageQueue.queue().offer(MapMessageBuilder.buildGetBriefPlayerInfosRetMessage(playerId, briefPlayerInfos));
            // 记录Debug日志
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDebugGameLogMessage(
                    FileDebugGameLogMessage.DebugLogType.DEBUGLOGTYPE_INFO,
                    "Get brief player infos."));
        } catch (MySQLNonTransientConnectionException ex) {
            // 数据库断线
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
            return false;
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障

            // TODO: 记录日志
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDebugGameLogMessage(
                    FileDebugGameLogMessage.DebugLogType.DEBUGLOGTYPE_ERROR,
                    "Get brief player infos error."));

            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
                }
            }
        }

        return true;
    }

    /**
     * 保存账单
     * @return false表示数据库断连
     */
    public static boolean handleSaveBill(Connection connection, int playerId, String passport, int currencyId, int amount, String memo, String orderno, int status, int step) {
        CallableStatement stmt = null;
        try {
            stmt = connection.prepareCall("{call save_bill(?,?,?,?,?,?,?,?)}");
            stmt.setInt(1, playerId);
            stmt.setString(2, passport);
            stmt.setString(3, orderno);
            stmt.setInt(4, currencyId);
            stmt.setInt(5, amount);
            stmt.setString(6, memo);
            stmt.setInt(7, status);
            stmt.setInt(8, step);

            stmt.executeUpdate();

            // 记录Debug日志
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDebugGameLogMessage(
                    FileDebugGameLogMessage.DebugLogType.DEBUGLOGTYPE_INFO,
                    "Player[" + playerId + "] insert bill info. " + orderno));
        } catch (MySQLNonTransientConnectionException ex) {
            // 数据库断线
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
            return false;
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障

            // TODO: 记录日志
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDebugGameLogMessage(
                    FileDebugGameLogMessage.DebugLogType.DEBUGLOGTYPE_ERROR,
                    "Player[" + playerId + "] insert bill info error. " + orderno));

            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
                }
            }
        }

        return true;
    }

    public static boolean handleSaveConsumerLog(Connection connection, int playerId, int consumerType, int goldNum, int goldType, String productId, int productType) {
        CallableStatement stmt = null;
        try {
            stmt = connection.prepareCall("{call save_consumer_log(?,?,?,?,?,?)}");
            stmt.setInt(1, playerId);
            stmt.setInt(2, consumerType);
            stmt.setInt(3, goldNum);
            stmt.setInt(4, goldType);
            stmt.setString(5, productId);
            stmt.setInt(6, productType);
            
            stmt.executeUpdate();            
        } catch (MySQLNonTransientConnectionException ex) {
            // 数据库断线
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
            return false;
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障

            // TODO: 记录日志
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDebugGameLogMessage(
                    FileDebugGameLogMessage.DebugLogType.DEBUGLOGTYPE_ERROR,
                    "Player[" + playerId + "] insert consumer log error."));

            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
                }
            }
        }

        return true;
    }

    /**
     *
     * @param connection
     * @param playerId
     * @param passport
     * @param code
     * @param time
     * @return false表示数据库断连
     */
    public static boolean handleGetcoupon(Connection connection, int playerId, String passport, String code, long time) {
        CallableStatement stmt = null;
        try {
            stmt = connection.prepareCall("{call save_coupon(?,?,?,?)}");
            stmt.setInt(1, playerId);
            stmt.setString(2, passport);
            stmt.setString(3, code);
            stmt.setTimestamp(4, new Timestamp(time));

            stmt.executeUpdate();

            // 记录Debug日志
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDebugGameLogMessage(
                    FileDebugGameLogMessage.DebugLogType.DEBUGLOGTYPE_INFO,
                    "Player[" + playerId + "] insert coupon info. " + code));
        } catch (MySQLNonTransientConnectionException ex) {
            // 数据库断线
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
            return false;
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障

            // TODO: 记录日志
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDebugGameLogMessage(
                    FileDebugGameLogMessage.DebugLogType.DEBUGLOGTYPE_ERROR,
                    "Player[" + playerId + "] insert coupon info error. " + code));

            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
                }
            }
        }

        return true;
    }

    /**
     *
     * @param connection
     * @param mailId
     * @param targetId
     * @param content
     * @param time
     * @return false表示数据库断连
     */
    public static boolean handleSaveMail(Connection connection, long mailId, int targetId, String title, String description, RewardProto rewardProto, long time) {
        CallableStatement stmt = null;
        try {
            stmt = connection.prepareCall("{call save_mail(?,?,?,?,?,?)}");
            stmt.setLong(1, mailId);
            stmt.setInt(2, targetId);

            stmt.setString(3, title);
            stmt.setString(4, description);

            if (rewardProto != null) {
                Blob blob = connection.createBlob();
                byte[] bytes = rewardProto.toByteArray();
                blob.setBytes(1, bytes);
                stmt.setBlob(5, blob);
            } else {
                stmt.setNull(5, java.sql.Types.BLOB);
            }

            stmt.setTimestamp(6, new Timestamp(time));

            stmt.executeUpdate();

            // 记录Debug日志
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDebugGameLogMessage(
                    FileDebugGameLogMessage.DebugLogType.DEBUGLOGTYPE_INFO,
                    "Insert mail info. " + mailId));
        } catch (MySQLNonTransientConnectionException ex) {
            // 数据库断线
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
            return false;
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障

            // TODO: 记录日志
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDebugGameLogMessage(
                    FileDebugGameLogMessage.DebugLogType.DEBUGLOGTYPE_ERROR,
                    "Insert mail info error. " + mailId));

            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
                }
            }
        }

        return true;
    }

    /**
     *
     * @param connection
     * @param playerId
     * @param battleId
     * @return false表示数据库断连
     */
    public static boolean handleGetNewMail(Connection connection, int playerId, long lastGlobalMaxMailId) {
        CallableStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = connection.prepareCall("{call get_mails(?, ?)}");

            stmt.setLong(1, lastGlobalMaxMailId);
            stmt.setInt(2, playerId);

            rs = stmt.executeQuery();

            LinkedList<Mail> mails = new LinkedList<Mail>();
            while (rs.next()) {
                long mailId = rs.getLong(1);
                String mailTitle = rs.getString(2);
                String mailDescription = rs.getString(3);

                RewardProto rewardProto = null;
                Blob blob = rs.getBlob(4);
                if (blob != null) {
                    try {
                        rewardProto = RewardProto.getDefaultInstance().newBuilderForType().mergeFrom(CodedInputStream.newInstance(blob.getBinaryStream())).build();
                    } catch (IOException ex) {
                        GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDebugGameLogMessage(
                                FileDebugGameLogMessage.DebugLogType.DEBUGLOGTYPE_ERROR,
                                "Get player[" + playerId + "] new mail[" + mailId + "] reward info error."));
                        GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDebugGameLogMessage(
                                FileDebugGameLogMessage.DebugLogType.DEBUGLOGTYPE_ERROR,
                                StackTraceUtil.getStackTrace(ex)));
                    }
                }

                Mail mail = new Mail(mailId,
                        mailTitle,
                        mailDescription,
                        (rewardProto == null) ? null : new CertainRewardInfo(rewardProto),
                        Consts.MAIL_STATUS_UNREAD);
                mails.add(mail);
            }

            ServerMessageQueue.queue().offer(MapMessageBuilder.buildGetNewMailRetMessage(playerId, mails));
        } catch (MySQLNonTransientConnectionException ex) {
            // 数据库断线
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
            return false;
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障

            // TODO: 记录日志
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDebugGameLogMessage(
                    FileDebugGameLogMessage.DebugLogType.DEBUGLOGTYPE_ERROR,
                    "Player[" + playerId + "] get new mails error."));

            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
                }
            }
        }

        return true;
    }

    public static String handleGetConsumerLog(Connection connection, int playerId, int consumerType, long beginTime, long endTime){        
        CallableStatement stmt = null;
        ResultSet rs = null;
        try {
            String strRet = "";
            
            stmt = connection.prepareCall("{call get_consumer_log(?,?,?,?)}");
            stmt.setInt(1, playerId);
            stmt.setInt(2, consumerType);            
            //stmt.setTimestamp(3, new Timestamp(beginTime));
            //stmt.setTimestamp(4, new Timestamp(endTime));
            stmt.setTimestamp(3, new Timestamp(GameServer.INSTANCE.getCurrentTime() - Consts.MILSECOND_TWO_DAY));
            stmt.setTimestamp(4, new Timestamp(GameServer.INSTANCE.getCurrentTime()));

            rs = stmt.executeQuery();
            strRet += "[";
            while (rs.next()) {
                strRet += "{\"goldnum\":";
                strRet += rs.getString(3);
                strRet += ", \"goldtype\":";
                strRet += rs.getString(4);
                strRet += ", \"productid\":\"";
                strRet += rs.getString(5);
                strRet += "\", \"productnum\":";
                strRet += rs.getString(6);
                strRet += ", \"time\":\"";
                strRet += rs.getString(7);
                strRet += "\"},";
            }
            if (strRet.length() > 1) {
                strRet = strRet.substring(0, strRet.length() - 1);
            }

            strRet += "]";
            return strRet;
        } catch (MySQLNonTransientConnectionException ex) {
            // 数据库断线
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
            return "";
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
            GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    GameLogger.getlogger().log(GameLogMessageBuilder.buildFileDBErrorGameLogMessage(StackTraceUtil.getStackTrace(ex)));
                }
            }
        }
        return "";
    }
}
